package jsyf.oa.dao.report.expense;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.report.expense.ReportCostEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2018/4/4.
 */
@Repository
public class ReportCostDao extends BaseDao {


    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ReportCostEntity.class);
    }

    public ReportCostDao() {
        super.iBaseEntity = new ReportCostEntity();
    }

//    public List<JSONObject> getReportCostByUserIdAndReport(String condition, Object[] params) {
//        StringBuffer sql = new StringBuffer();
//        sql.append(" SELECT \n");
//        sql.append(" T_WRT_COST.ID AS id, \n");
//        sql.append(" T_SYS_USER.NAME AS name, \n");
//        sql.append(" T_SYS_USER.TEL AS tel, \n");
//        sql.append(" T_WRT_MAIN.REPORT_DATE AS reportDate, \n");
//        sql.append(" T_WRT_COST.COST_BIG_NAME AS costBigName, \n");
//        sql.append(" T_WRT_COST.COST_SMALL_NAME AS costBigSmall, \n");
//        sql.append(" T_WRT_COST.MONEY AS money, \n");
//        sql.append(" T_WRT_COST.INVOICE_FLAG AS invoiceFlag, \n");
//        sql.append(" T_WRT_COST.REMARK AS remark, \n");
//        sql.append(" T_WRT_COST.START AS start \n");
//        sql.append(" FROM \n");
//        sql.append(" T_WRT_COST \n");
//        sql.append(" LEFT JOIN T_WRT_MAIN ON T_WRT_COST.W_ID = T_WRT_MAIN.ID \n");
//        sql.append(" LEFT JOIN T_SYS_USER ON T_WRT_MAIN.USER_ID = T_SYS_USER.ID \n");
//        sql.append(" WHERE 1=1 \n");
//        sql.append(condition);
//        sql.append(" ORDER BY T_SYS_USER. NAME,T_WRT_MAIN.REPORT_DATE DESC ");
//        return super.queryForJsonList(sql.toString(), params);
//    }


   /* public List<JSONObject> getReportCost(String condition, Object[] params){
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" v_wrt_cost.id, \n");
        sql.append(" v_wrt_cost.tel, \n");
        sql.append(" v_wrt_cost.reportDate, \n");
        sql.append(" v_wrt_cost.costBigName, \n");
        sql.append(" v_wrt_cost.costBigSmall, \n");
        sql.append(" v_wrt_cost.money, \n");
        sql.append(" v_wrt_cost.invoiceFlag, \n");
        sql.append(" v_wrt_cost.remark, \n");
        sql.append(" v_wrt_cost.start \n");
        sql.append(" FROM v_wrt_cost \n");
        sql.append(" WHERE 1=1  \n");
        sql.append(condition);
        sql.append(" ORDER BY reportDate DESC ");
        return super.queryForJsonList(sql.toString(), params);
    }


    public List<JSONObject> getReportCostBigRegion(String userId,String dateTime){
        int len = dateTime.length();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" sysUser.userId, \n");
        sql.append(" sysUser.roleName, \n");
        sql.append(" sysUser.`name` as userName, \n");
        sql.append(" sysUser.regionName, \n");
        sql.append(" sysUser.reportDate, \n");
        sql.append(" IFNULL(sysMoney.money,0) as money, \n");
        sql.append(" IFNULL(sysMoney.claimMoney,0) as claimMoney, \n");
        sql.append(" IFNULL(money - claimMoney,0) as unClaimMoney \n");
        sql.append(" FROM \n");
        sql.append(" (SELECT \n");
        sql.append(" `t_sys_user`.`ID` AS `userId`, \n");
        sql.append(" `t_sys_user`.`NAME` AS `name`, \n");
        sql.append(" `t_sys_role_name`.`NAME` AS `roleName`, \n");
        if (len == 10) {
            sql.append(" '" + dateTime + "' as reportDate, \n");
        } else {
            sql.append(" LEFT('" + dateTime + "',7) as reportDate, \n");
        }
        sql.append(" CASE \n");
        sql.append(" t_sys_role_name.AREA_TYPE =0 AND FRONT_FLAG=1 \n");
        sql.append(" THEN \n");
        sql.append(" (SELECT `NAME` FROM t_sys_region WHERE PARENT_ID <> 0 AND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = t_sys_user.ID) LIMIT 0,1) \n");
        sql.append(" ELSE \n");
        sql.append(" (SELECT `NAME` FROM t_sys_region WHERE PARENT_ID = 0 AND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = t_sys_user.ID) LIMIT 0,1) \n");
        sql.append(" END  regionName \n");
        sql.append(" FROM \n");
        sql.append(" `t_sys_user` \n");
        sql.append(" LEFT JOIN `t_sys_user_leave` ON `t_sys_user`.`ID` = `t_sys_user_leave`.`USER_ID` \n");
        sql.append(" LEFT JOIN `t_sys_role` ON `t_sys_user`.`ROLE_ID` = `t_sys_role`.`ID` \n");
        sql.append(" LEFT JOIN `t_sys_role_name` ON `t_sys_role`.`ROLE_NAME_ID` = `t_sys_role_name`.`ID` \n");
        sql.append(" WHERE 1 = 1 \n");
        sql.append(" AND ( \n");
        sql.append(" t_sys_user.JOB_STATE <> 1 \n");
        sql.append(" OR ( \n");
        sql.append(" t_sys_user.JOB_STATE = 1 \n");
        if (len == 10) {
            sql.append(" AND t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' ))\n");
        } else {
            sql.append(" AND LEFT(t_sys_user_leave.LEAVE_DATE,7) ='" + dateTime + "' ))\n");
        }
        sql.append(" AND (t_sys_user.ID = '" + userId + "' OR t_sys_user.PARENT_ID = '" + userId + "') \n");
        sql.append(" ORDER BY t_sys_role.SORT_NO,t_sys_user.SORT_NO \n");
        sql.append(" ) as sysUser \n");
        sql.append(" LEFT JOIN  \n");
        sql.append(" ( SELECT \n");
        sql.append(" t_wrt_main.USER_ID as userId, \n");
        sql.append(" LEFT(t_wrt_main.REPORT_DATE,7) as reportDate, \n");
        sql.append(" SUM(t_wrt_cost.MONEY) as money, \n");
        sql.append(" SUM(IF(t_wrt_cost.`START` = 1,t_wrt_cost.MONEY,0)) as claimMoney \n");
        sql.append(" FROM \n");
        sql.append(" t_wrt_main \n");
        sql.append(" LEFT JOIN t_wrt_cost ON \n");
        sql.append(" t_wrt_main.ID = t_wrt_cost.W_ID \n");
        sql.append(" WHERE 1 = 1  \n");
        if (len == 10) {
            sql.append(" AND t_wrt_main.REPORT_DATE ='" + dateTime + "' \n");
        } else {
            sql.append(" AND LEFT(t_wrt_main.REPORT_DATE,7)= '" + dateTime + "' \n");
        }
        sql.append(" AND (t_wrt_main.USER_ID = '" + userId + "'\n");
        sql.append(" OR t_wrt_main.USER_ID in(SELECT ID FROM t_sys_user WHERE PARENT_ID = '" + userId + "' ))\n");
        sql.append(" GROUP BY t_wrt_main.USER_ID,LEFT(t_wrt_main.REPORT_DATE,7))  \n");
        sql.append(" as sysMoney ON sysUser.userId = sysMoney.userId and sysUser.reportDate = sysMoney.reportDate \n");
        System.out.println(sql.toString());
        return super.queryForJsonList(sql.toString());

    }

    public List<JSONObject> getPersonalDetail(String userId,String dateTime,String start){
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" T_WRT_COST.ID AS id,  \n");
        sql.append(" T_SYS_USER.NAME AS name,  \n");
        sql.append(" T_SYS_USER.TEL AS tel, \n");
        sql.append(" T_WRT_MAIN.REPORT_DATE AS reportDate, \n");
        sql.append(" T_WRT_COST.COST_BIG_NAME AS costBigName,  \n");
        sql.append(" T_WRT_COST.COST_SMALL_NAME AS costBigSmall,   \n");
        sql.append(" T_WRT_COST.MONEY AS money,  \n");
        sql.append(" T_WRT_COST.INVOICE_FLAG AS invoiceFlag,  \n");
        sql.append(" T_WRT_COST.REMARK AS remark,  \n");
        sql.append(" T_WRT_COST.START AS start  \n");
        sql.append(" FROM \n");
        sql.append(" T_WRT_COST \n");
        sql.append(" LEFT JOIN T_WRT_MAIN ON T_WRT_COST.W_ID = T_WRT_MAIN.ID  \n");
        sql.append(" LEFT JOIN T_SYS_USER ON T_WRT_MAIN.USER_ID = T_SYS_USER.ID \n");
        sql.append(" WHERE 1 =1 \n");
        sql.append(" AND t_sys_user.ID='"+userId+"' \n");
        sql.append(" AND `START`='"+start+"' \n");
        if (dateTime.length() == 10) {
            sql.append("AND T_WRT_MAIN.REPORT_DATE = '"+dateTime+"' \n");
        } else {
            sql.append("AND LEFT(T_WRT_MAIN.REPORT_DATE,7) ='"+dateTime+"' \n");
        }
        sql.append("ORDER BY T_WRT_MAIN.REPORT_DATE,T_WRT_COST.COST_SMALL_NAME");
        return super.queryForJsonList(sql.toString());
    }
*/

    public List<JSONObject> getExceptPersonalByUserIdAndDateTime(String userId, String dateTime, String start, String todayTime) {
        StringBuilder sbSql = new StringBuilder();
        int length = dateTime.length();
        sbSql.append("SELECT sysUser.*,sysCost.money,sysCost.bxState,sysCost.costSmallName,sysCost.invoiceFlag,sysCost.remark,sysCost.costId,sysCost.reportDate\n" +
                ",sysCost.auditFlag FROM (\n" +
                "SELECT v_sys_part_time_for_report.userId,v_sys_part_time_for_report.userName\n" +
                ",t_sys_role_name.`NAME` as roleName,t_sys_depart.`NAME` as departName\n" +
                "FROM v_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_user_leave ON v_sys_part_time_for_report.userId=t_sys_user_leave.USER_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=v_sys_part_time_for_report.departId\n" +
                "WHERE 1 = 1\n" +
                "AND (v_sys_part_time_for_report.jobState <> 1 OR (v_sys_part_time_for_report.jobState = 1 AND\n");
        //  " t_sys_user_leave.LEAVE_DATE >'" + dateTime + "'))\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sbSql.append("t_sys_user_leave.LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sbSql.append("t_sys_user_leave.LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sbSql.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
        }
        sbSql.append(")) AND (\n" +
                "  userId = '" + userId + "' \n" +
                "\tOR parentId = '" + userId + "'\n" +
                "  OR (\n" +
                "\t\t\t\t\tuserId in(\n" +
                "\t\t\t\t\t\t\t\t\t\tSELECT USER_ID FROM t_sys_user_2_region \n" +
                "\t\t\t\t\t\t\t\t\t\tWHERE \n" +
                "\t\t\t\t\t\t\t\t\t\tREGION_ID in(\n" +
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = '" + userId + "'\n" +
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\tAND t_sys_role_name.FRONT_FLAG = 1\n" +
                "\t\t\t\t)\n" +
                ")\n" +
                "GROUP BY userId,departId\n" +
                "ORDER BY roleSortNo,userSortNo) as sysUser\n" +
                "LEFT JOIN \n" +
                "(\n" +
                "\tSELECT v_wrt_cost_for_report.money,v_wrt_cost_for_report.bxState,v_wrt_cost_for_report.costSmallName,\n" +
                "v_wrt_cost_for_report.invoiceFlag,v_wrt_cost_for_report.userId,v_wrt_cost_for_report.costId\n" +
                ",v_wrt_cost_for_report.remark,v_wrt_cost_for_report.reportDate,v_wrt_cost_for_report.auditFlag\n" +
                "\tFROM v_wrt_cost_for_report\n" +
                "WHERE left(v_wrt_cost_for_report.reportDate,10) like ");
        if (length == 10) {
            sbSql.append("'" + dateTime + "'\n");
        } else {
            sbSql.append("'" + dateTime + "%'\n");
        }
        sbSql.append(") as sysCost ON sysUser.userId = sysCost.userId\n" +
                "WHERE money is not  null AND bxState is not null and invoiceFlag is not null\n");
        if (StringUtils.isNotBlank(start) && StringUtils.isNotEmpty(start)) {
            sbSql.append(" AND bxState = " + start + "\n");
        }
        return super.queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getForPersonalByUserIdAndDateTime(String userId, String dateTime, String start) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT t_sys_user.name as userName,v_wrt_cost_for_report.money,v_wrt_cost_for_report.bxState," +
                "v_wrt_cost_for_report.costSmallName,v_wrt_cost_for_report.invoiceFlag,v_wrt_cost_for_report.userId\n" +
                ",v_wrt_cost_for_report.remark,v_wrt_cost_for_report.reportDate as reportDate,v_wrt_cost_for_report.costId\n" +
                ",v_wrt_cost_for_report.auditFlag FROM \n" +
                "v_wrt_cost_for_report left join t_sys_user on t_sys_user.id=v_wrt_cost_for_report.userId\n" +
                "WHERE 1 = 1\n" +
                "  AND userId = '" + userId + "'\n" +
                "  AND reportDate LIKE '" + dateTime + "%'\n ");
        if (StringUtils.isNotBlank(start) && StringUtils.isNotEmpty(start)) {
            sbSql.append(" AND bxState = " + start + "\n");
        }
        sbSql.append("order by v_wrt_cost_for_report.reportDate desc");
        return super.queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getForPersonalSumByUserIdAndDateTime(String userId, String dateTime) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT sum(v_wrt_cost_for_report.money) sumMoney,v_wrt_cost_for_report.costSmallName\n" +
                "FROM \n" +
                "v_wrt_cost_for_report left join t_sys_user on t_sys_user.id=v_wrt_cost_for_report.userId\n" +
                "WHERE 1 = 1\n" +
                "  AND userId = '"+userId+"'\n" +
                "  AND reportDate LIKE '"+dateTime+"%'\n" +
                " GROUP BY v_wrt_cost_for_report.costSmallId,v_wrt_cost_for_report.costSmallName");
        return super.queryForJsonList(sbSql.toString());
    }


}